const { pool } = require('../config/database');

class Food {
  // 获取所有食物
  static async getAllFoods() {
    try {
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               r.name as refrigerator_name, r.location as refrigerator_location
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        ORDER BY f.created_at DESC
      `);
      return rows;
    } catch (error) {
      console.error('获取食物列表失败:', error.message);
      throw error;
    }
  }

  // 根据冰箱ID获取食物
  static async getFoodsByRefrigerator(refrigeratorId) {
    try {
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               r.name as refrigerator_name, r.location as refrigerator_location
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        WHERE f.refrigerator_id = ?
        ORDER BY f.expiry_date ASC, f.reminder_date ASC
      `, [refrigeratorId]);
      return rows;
    } catch (error) {
      console.error('根据冰箱获取食物失败:', error.message);
      throw error;
    }
  }

  // 根据温度状态获取食物（支持多选）
  static async getFoodsByTemperature(temperatureStatuses) {
    try {
      // 如果传入的是字符串，转换为数组
      if (typeof temperatureStatuses === 'string') {
        temperatureStatuses = [temperatureStatuses];
      }
      
      // 构建 IN 查询的占位符
      const placeholders = temperatureStatuses.map(() => '?').join(',');
      
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               r.name as refrigerator_name, r.location as refrigerator_location
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        WHERE f.temperature_status IN (${placeholders})
        ORDER BY f.expiry_date ASC
      `, temperatureStatuses);
      return rows;
    } catch (error) {
      console.error('根据温度状态获取食物失败:', error.message);
      throw error;
    }
  }

  // 根据冰箱ID和温度状态获取食物（支持组合查询）
  static async getFoodsByRefrigeratorAndTemperature(options = {}) {
    try {
      const { refrigeratorId, temperatureStatuses } = options;
      
      // 构建动态WHERE条件
      let whereConditions = [];
      let queryParams = [];
      
      // 添加冰箱ID条件
      if (refrigeratorId) {
        whereConditions.push('f.refrigerator_id = ?');
        queryParams.push(refrigeratorId);
      }
      
      // 添加温度状态条件
      if (temperatureStatuses && temperatureStatuses.length > 0) {
        // 如果传入的是字符串，转换为数组
        let statuses = typeof temperatureStatuses === 'string' ? [temperatureStatuses] : temperatureStatuses;
        
        const placeholders = statuses.map(() => '?').join(',');
        whereConditions.push(`f.temperature_status IN (${placeholders})`);
        queryParams.push(...statuses);
      }
      
      // 构建完整的WHERE子句
      const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';
      
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               r.name as refrigerator_name, r.location as refrigerator_location
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        ${whereClause}
        ORDER BY f.expiry_date ASC, f.created_at DESC
      `, queryParams);
      
      return rows;
    } catch (error) {
      console.error('根据冰箱和温度状态获取食物失败:', error.message);
      throw error;
    }
  }

  // 获取即将过期的食物
  static async getExpiringFoods(days = 3) {
    try {
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               r.name as refrigerator_name, r.location as refrigerator_location,
               DATEDIFF(f.expiry_date, CURDATE()) as days_to_expiry
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        WHERE f.expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL ? DAY)
        ORDER BY f.expiry_date ASC
      `, [days]);
      return rows;
    } catch (error) {
      console.error('获取即将过期食物失败:', error.message);
      throw error;
    }
  }

  // 获取已过期的食物
  static async getExpiredFoods() {
    try {
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               r.name as refrigerator_name, r.location as refrigerator_location,
               DATEDIFF(CURDATE(), f.expiry_date) as days_expired
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        WHERE f.expiry_date < CURDATE()
        ORDER BY f.expiry_date ASC
      `);
      return rows;
    } catch (error) {
      console.error('获取已过期食物失败:', error.message);
      throw error;
    }
  }

  // 获取需要提醒的食物
  static async getFoodsNeedingReminder() {
    try {
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               r.name as refrigerator_name, r.location as refrigerator_location
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        WHERE f.reminder_date <= CURDATE()
        ORDER BY f.reminder_date ASC
      `);
      return rows;
    } catch (error) {
      console.error('获取需要提醒的食物失败:', error.message);
      throw error;
    }
  }

  // 根据ID获取食物详情
  static async getFoodById(id) {
    try {
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               ft.can_room_temp, ft.can_refrigerated, ft.can_frozen,
               r.name as refrigerator_name, r.location as refrigerator_location
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        WHERE f.id = ?
      `, [id]);

      if (rows.length === 0) return null;
      return rows[0];
    } catch (error) {
      console.error('获取食物详情失败:', error.message);
      throw error;
    }
  }

  // 创建食物
  static async createFood(foodData) {
    try {
      const { 
        notes, put_in_date, shelf_life_days, expiry_date, reminder_date, 
        temperature_status, template_id, refrigerator_id 
      } = foodData;
      
      const [result] = await pool.execute(`
        INSERT INTO foods (notes, put_in_date, shelf_life_days, expiry_date, 
                          reminder_date, temperature_status, template_id, refrigerator_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      `, [notes, put_in_date, shelf_life_days, expiry_date, reminder_date, 
          temperature_status, template_id, refrigerator_id]);

      return {
        id: result.insertId,
        ...foodData
      };
    } catch (error) {
      console.error('创建食物失败:', error.message);
      throw error;
    }
  }

  // 更新食物
  static async updateFood(id, foodData) {
    try {
      const { 
        notes, put_in_date, shelf_life_days, expiry_date, reminder_date, 
        temperature_status, template_id, refrigerator_id 
      } = foodData;
      
      await pool.execute(`
        UPDATE foods 
        SET notes = ?, put_in_date = ?, shelf_life_days = ?, expiry_date = ?, 
            reminder_date = ?, temperature_status = ?, template_id = ?, 
            refrigerator_id = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [notes, put_in_date, shelf_life_days, expiry_date, reminder_date, 
          temperature_status, template_id, refrigerator_id, id]);

      return await this.getFoodById(id);
    } catch (error) {
      console.error('更新食物失败:', error.message);
      throw error;
    }
  }

  // 删除食物
  static async deleteFood(id) {
    try {
      const [result] = await pool.execute(`
        DELETE FROM foods WHERE id = ?
      `, [id]);

      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除食物失败:', error.message);
      throw error;
    }
  }

  // 搜索食物
  static async searchFoods(keyword) {
    try {
      const [rows] = await pool.execute(`
        SELECT f.id, f.notes, f.shelf_life_days, f.temperature_status, 
               f.template_id, f.refrigerator_id,
               DATE_FORMAT(f.put_in_date, '%Y-%m-%d') as put_in_date,
               DATE_FORMAT(f.expiry_date, '%Y-%m-%d') as expiry_date,
               DATE_FORMAT(f.reminder_date, '%Y-%m-%d') as reminder_date,
               DATE_FORMAT(f.created_at, '%Y-%m-%d') as created_at,
               DATE_FORMAT(f.updated_at, '%Y-%m-%d') as updated_at,
               ft.name as template_name, ft.icon as template_icon, ft.food_type,
               r.name as refrigerator_name, r.location as refrigerator_location
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        LEFT JOIN refrigerators r ON f.refrigerator_id = r.id
        WHERE f.notes LIKE ? OR ft.name LIKE ? OR ft.food_type LIKE ?
        ORDER BY f.created_at DESC
      `, [`%${keyword}%`, `%${keyword}%`, `%${keyword}%`]);
      return rows;
    } catch (error) {
      console.error('搜索食物失败:', error.message);
      throw error;
    }
  }

  // 批量添加食物（相同模板但不同备注）
  static async batchCreateFoods(foodList) {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();

      const createdFoods = [];
      for (const foodData of foodList) {
        const { 
          notes, put_in_date, shelf_life_days, expiry_date, reminder_date, 
          temperature_status, template_id, refrigerator_id 
        } = foodData;

        const [result] = await connection.execute(`
          INSERT INTO foods (notes, put_in_date, shelf_life_days, expiry_date, 
                            reminder_date, temperature_status, template_id, refrigerator_id)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        `, [notes, put_in_date, shelf_life_days, expiry_date, reminder_date, 
            temperature_status, template_id, refrigerator_id]);

        createdFoods.push({
          id: result.insertId,
          ...foodData
        });
      }

      await connection.commit();
      return createdFoods;
    } catch (error) {
      await connection.rollback();
      console.error('批量创建食物失败:', error.message);
      throw error;
    } finally {
      connection.release();
    }
  }

  // 获取食物统计信息
  static async getFoodStats() {
    try {
      const [totalCount] = await pool.execute(`
        SELECT COUNT(*) as total FROM foods
      `);
      
      const [temperatureStats] = await pool.execute(`
        SELECT 
          temperature_status,
          COUNT(*) as count
        FROM foods
        GROUP BY temperature_status
      `);
      
      const [expiryStats] = await pool.execute(`
        SELECT 
          CASE 
            WHEN expiry_date < CURDATE() THEN 'expired'
            WHEN expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 3 DAY) THEN 'expiring_soon'
            ELSE 'normal'
          END as expiry_status,
          COUNT(*) as count
        FROM foods
        GROUP BY expiry_status
      `);

      const [typeStats] = await pool.execute(`
        SELECT 
          ft.food_type,
          COUNT(*) as count
        FROM foods f
        LEFT JOIN food_templates ft ON f.template_id = ft.id
        GROUP BY ft.food_type
        ORDER BY count DESC
      `);

      return {
        total: totalCount[0].total,
        by_temperature: temperatureStats,
        by_expiry: expiryStats,
        by_type: typeStats
      };
    } catch (error) {
      console.error('获取食物统计失败:', error.message);
      throw error;
    }
  }

  // 移动食物到其他冰箱
  static async moveFoodToRefrigerator(foodId, newRefrigeratorId) {
    try {
      await pool.execute(`
        UPDATE foods 
        SET refrigerator_id = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [newRefrigeratorId, foodId]);

      return await this.getFoodById(foodId);
    } catch (error) {
      console.error('移动食物失败:', error.message);
      throw error;
    }
  }

  // 更新食物温度状态
  static async updateFoodTemperature(foodId, temperatureStatus) {
    try {
      await pool.execute(`
        UPDATE foods 
        SET temperature_status = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [temperatureStatus, foodId]);

      return await this.getFoodById(foodId);
    } catch (error) {
      console.error('更新食物温度状态失败:', error.message);
      throw error;
    }
  }

  // 计算保质期状态
  static calculateShelfLifeStatus(putInDate, expiryDate, currentDate = new Date()) {
    const putIn = new Date(putInDate);
    const expiry = new Date(expiryDate);
    const current = new Date(currentDate);
    
    // 总保质期天数
    const totalDays = Math.ceil((expiry - putIn) / (1000 * 60 * 60 * 24));
    // 已过去的天数
    const passedDays = Math.ceil((current - putIn) / (1000 * 60 * 60 * 24));
    // 剩余天数
    const remainingDays = Math.ceil((expiry - current) / (1000 * 60 * 60 * 24));
    
    // 计算过去时间的百分比
    const passedPercentage = (passedDays / totalDays) * 100;
    
    let status = 'normal';
    let statusText = '正常';
    let statusColor = 'green';
    
    if (passedPercentage > 100 || remainingDays < 0) {
      status = 'expired';
      statusText = '过期';
      statusColor = 'red';
    } else if (passedPercentage >= 60) {
      status = 'near_expiry';
      statusText = '临期';
      statusColor = 'orange';
    }
    
    return {
      status,
      statusText,
      statusColor,
      totalDays,
      passedDays: Math.max(0, passedDays),
      remainingDays: Math.max(0, remainingDays),
      passedPercentage: Math.min(100, Math.max(0, passedPercentage)),
      remainingPercentage: Math.max(0, 100 - passedPercentage)
    };
  }

  // 获取带保质期状态的所有食物
  static async getAllFoodsWithShelfLifeStatus() {
    try {
      const foods = await this.getAllFoods();
      return foods.map(food => ({
        ...food,
        shelf_life_status: this.calculateShelfLifeStatus(food.put_in_date, food.expiry_date)
      }));
    } catch (error) {
      console.error('获取带保质期状态的食物失败:', error.message);
      throw error;
    }
  }

  // 根据保质期状态获取食物
  static async getFoodsByShelfLifeStatus(status) {
    try {
      const foods = await this.getAllFoods();
      return foods.filter(food => {
        const shelfLifeStatus = this.calculateShelfLifeStatus(food.put_in_date, food.expiry_date);
        return shelfLifeStatus.status === status;
      }).map(food => ({
        ...food,
        shelf_life_status: this.calculateShelfLifeStatus(food.put_in_date, food.expiry_date)
      }));
    } catch (error) {
      console.error('根据保质期状态获取食物失败:', error.message);
      throw error;
    }
  }

  // 获取保质期状态统计
  static async getShelfLifeStatusStats() {
    try {
      const foods = await this.getAllFoods();
      const stats = {
        normal: 0,
        near_expiry: 0,
        expired: 0,
        total: foods.length
      };

      foods.forEach(food => {
        const shelfLifeStatus = this.calculateShelfLifeStatus(food.put_in_date, food.expiry_date);
        stats[shelfLifeStatus.status]++;
      });

      return {
        ...stats,
        normal_percentage: ((stats.normal / stats.total) * 100).toFixed(1),
        near_expiry_percentage: ((stats.near_expiry / stats.total) * 100).toFixed(1),
        expired_percentage: ((stats.expired / stats.total) * 100).toFixed(1)
      };
    } catch (error) {
      console.error('获取保质期状态统计失败:', error.message);
      throw error;
    }
  }
}

module.exports = Food;
